/**
 * 
 */
package com.magus.project.yddemo.service;

import java.sql.Date;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.DoubleSummaryStatistics;
import java.util.List;
import java.util.stream.Collectors;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.magus.framework.core.persistence.JpaSearchUtils;
import com.magus.framework.core.persistence.SearchFilter;
import com.magus.framework.core.repository.BaseRepository;
import com.magus.framework.core.service.BaseService;
import com.magus.project.yddemo.entity.IndicatorMapperData;
import com.magus.project.yddemo.entity.IndicatorMapperReport1;
import com.magus.project.yddemo.repository.IndicatorMapperReport1Repository;

/**
 * @author wenyu.han
 * @date 2018-03-19 13:10:46
 */

@Service
@Transactional(readOnly = true)
public class IndicatorMapperReport1Service extends BaseService<IndicatorMapperReport1, String> {

	@Autowired
	private IndicatorMapperReport1Repository reportRepository;

	@Autowired
	private IndicatorMapperDataService mapperDataService;

	@Override
	protected BaseRepository<IndicatorMapperReport1, String> getBaseRepository() {
		return reportRepository;
	}

	// 6000千瓦及以下电厂合计行号
	static final Integer ROW = 70;

	// 数据起始行号
	static final Integer ROW_INDEX = 12;

	/**
	 * 每行的固定列用##符号分开
	 */
	static final String[] ALL_ROW_NAME = new String[] { "一、总计##01##", "（一）水电##0101##", "（二）火电##0102##", "（三）核电##0103##",
			"（四）风电##0104##", "（五）太阳能##0105##", "（六）其他##0106##", "二、6000千瓦及以上电厂合计##02##", "（一）水电##0201##",
			"其中：抽水蓄能##020101##", "（二）火电##0202##", "1、燃煤##020201##", "其中：煤矸石发电##02020101##", "2、燃气##020202##",
			"其中：煤层气发电##02020201##", "沼气发电##02020202##", "3、燃油##020203##", "4、其他##020204##", "其中：余温、余压、余气发电##02020401##",
			"垃圾焚烧发电##02020402##", "秸秆、蔗渣、林木质发电##02020403##", "（三）核电##0203##", "（四）风电##0204##", "（五）太阳能##0205##",
			"（六）其他##0206##", "1、地热能##020601##", "2、海洋能##020602##", "3、其他##020603##", "三、6000千瓦及以上电厂明细##03##",
			"广东粤电新丰江发电有限责任公司##03001##", "广东粤电枫树坝发电有限责任公司##03002##", "广东粤电青溪发电有限责任公司##03003##",
			"广东粤电长湖发电有限责任公司##03004##", "广东粤电南水发电有限责任公司##03005##", "广东粤电长潭发电有限责任公司##03006##", "广东粤电流溪河发电有限责任公司##03007##",
			"大埔县梅江蓬辣滩水电站有限公司##03008##", "天生桥一级水电开发有限责任公司水力发电厂##03009##", "临沧粤电能源有限公司新塘房电厂##03010##",
			"临沧粤电能源有限公司南荣田电厂##03011##", "广东电力发展股份有限公司沙角A电厂##03012##", "广东粤电集团有限公司沙角C电厂##03013##",
			"广东省粤电集团有限公司珠海发电厂##03014##", "广东粤华发电有限责任公司##03015##", "湛江电力有限公司##03016##", "广东省韶关粤江发电有限责任公司##03017##",
			"茂名臻能热电有限公司##03018##", "广东粤嘉电力有限公司##03019##", "广东粤电云河发电有限公司##03020##", "广东省粤泷发电有限责任公司##03021##",
			"广东珠海金湾发电有限公司##03022##", "广东红海湾发电有限公司##03023##", "广东粤电靖海发电有限公司##03024##", "广东惠州平海发电厂有限公司##03025##",
			"贵州粤黔电力有限责任公司##03026##", "湛江中粤能源有限公司##03027##", "广东粤电大埔发电有限公司##03028##", "广东惠州天然气发电有限公司##03029##",
			"深圳市广前电力有限公司##03030##", "广东粤电湛江生物质发电有限公司##03031##", "广东粤电石碑山风能开发有限公司##03032##", "广东粤电湛江风力发电有限公司##03033##",
			"惠来风力发电有限公司##03034##", "广东粤电电白风力发电有限公司##03035##", "贵州粤电六盘水综合能源有限公司##03036##", "贵州粤电遵义风能有限公司仙人山风电场##03037##",
			"贵州粤电石阡风能有限公司大顶山风电场##03038##", "广州开发区粤电新能源有限公司##03039##", "广州粤电阳西新能源有限公司##03040##", "四、6000千瓦及以下电厂合计##04##",
			"（一）水电##0401##", "（二）火电##0402##", "（三）风电##0403##", "（四）太阳能##0404##", "（五）其他##0405##" };

	/**
	 * ########指标按照报表行顺序进行排列######## ########如果指标明细中为空的指标，用""标识########
	 * 年初生产能力_6000千瓦及以上电厂全部指标
	 */
	static final String[] NCSC_All = new String[] { "XFJ_MPRL_M_M_M_SUM_M_CTST", "FSB_MPRL_M_M_M_SUM_M_CTST",
			"QXH_MPRL_M_M_M_SUM_M_CTST", "CHH_MPRL_M_M_M_SUM_M_CTST", "NSH_MPRL_M_M_M_SUM_M_CTST",
			"CTH_MPRL_M_M_M_SUM_M_CTST", "LXH_MPRL_M_M_M_SUM_M_CTST", "PLT_MPRL_M_M_M_SUM_M_CTST",
			"TSQ_MPRL_M_M_M_SUM_M_CTST", "LCC_MPRL_M_M_M_SUM_M_CTST", "", "SJA_MPRL_M_M_M_SUM_M_CTST",
			"SJC_MPRL_M_M_M_SUM_M_CTST", "ZHP_MPRL_M_M_M_SUM_M_CTST", "HPP_MPRL_M_M_M_SUM_M_CTST",
			"ZJP_MPRL_M_M_M_SUM_M_CTST", "SGY_MPRL_M_M_M_SUM_M_CTST", "MMP_MPRL_M_M_M_SUM_M_CTST",
			"MXP_MPRL_M_M_M_SUM_M_CTST", "YHP_MPRL_M_M_M_SUM_M_CTST", "YLP_MPRL_M_M_M_SUM_M_CTST",
			"JWP_MPRL_M_M_M_SUM_M_CTST", "HHW_MPRL_M_M_M_SUM_M_CTST", "JHP_MPRL_M_M_M_SUM_M_CTST",
			"PHP_MPRL_M_M_M_SUM_M_CTST", "GZH_MPRL_M_M_M_SUM_M_CTST", "TSP_MPRL_M_M_M_SUM_M_CTST",
			"DPP_MPRL_M_M_M_SUM_M_CTST", "HZL_MPRL_M_M_M_SUM_M_CTST", "GQL_MPRL_M_M_M_SUM_M_CTST",
			"SWF_MPRL_M_M_M_SUM_M_CTST", "SBS_MPRL_M_M_M_SUM_M_CTST", "ZJW_MPRL_M_M_M_SUM_M_CTST",
			"LCC_MPRL_M_M_M_SUM_M_CTST", "", "HLW_MPRL_M_M_M_SUM_M_CTST", "DBW_MPRL_M_M_M_SUM_M_CTST",
			"PXW_MPRL_M_M_M_SUM_M_CTST", "ZYW_MPRL_M_M_M_SUM_M_CTST", "SQW_MPRL_M_M_M_SUM_M_CTST" };
	/**
	 * 年初生产能力_6000千瓦及以上水电全部指标
	 */
	static final String[] NCSC_SD = new String[] { "XFJ_MPRL_M_M_M_SUM_M_CTST", "FSB_MPRL_M_M_M_SUM_M_CTST",
			"QXH_MPRL_M_M_M_SUM_M_CTST", "CHH_MPRL_M_M_M_SUM_M_CTST", "NSH_MPRL_M_M_M_SUM_M_CTST",
			"CTH_MPRL_M_M_M_SUM_M_CTST", "LXH_MPRL_M_M_M_SUM_M_CTST", "PLT_MPRL_M_M_M_SUM_M_CTST",
			"TSQ_MPRL_M_M_M_SUM_M_CTST", "LCC_MPRL_M_M_M_SUM_M_CTST", "" };
	/**
	 * 年初生产能力_6000千瓦及以上火电全部指标
	 */
	static final String[] NCSC_HD = new String[] { "SJA_MPRL_M_M_M_SUM_M_CTST", "SJC_MPRL_M_M_M_SUM_M_CTST",
			"ZHP_MPRL_M_M_M_SUM_M_CTST", "HPP_MPRL_M_M_M_SUM_M_CTST", "ZJP_MPRL_M_M_M_SUM_M_CTST",
			"SGY_MPRL_M_M_M_SUM_M_CTST", "MMP_MPRL_M_M_M_SUM_M_CTST", "MXP_MPRL_M_M_M_SUM_M_CTST",
			"YHP_MPRL_M_M_M_SUM_M_CTST", "YLP_MPRL_M_M_M_SUM_M_CTST", "JWP_MPRL_M_M_M_SUM_M_CTST",
			"HHW_MPRL_M_M_M_SUM_M_CTST", "JHP_MPRL_M_M_M_SUM_M_CTST", "PHP_MPRL_M_M_M_SUM_M_CTST",
			"GZH_MPRL_M_M_M_SUM_M_CTST", "TSP_MPRL_M_M_M_SUM_M_CTST", "DPP_MPRL_M_M_M_SUM_M_CTST",
			"HZL_MPRL_M_M_M_SUM_M_CTST", "GQL_MPRL_M_M_M_SUM_M_CTST", "SWF_MPRL_M_M_M_SUM_M_CTST" };
	/**
	 * 年初生产能力_6000千瓦及以上火电燃煤指标
	 */
	static final String[] NCSC_HD_RM = new String[] { "SJA_MPRL_M_M_M_SUM_M_CTST", "SJC_MPRL_M_M_M_SUM_M_CTST",
			"ZHP_MPRL_M_M_M_SUM_M_CTST", "HPP_MPRL_M_M_M_SUM_M_CTST", "ZJP_MPRL_M_M_M_SUM_M_CTST",
			"SGY_MPRL_M_M_M_SUM_M_CTST", "MMP_MPRL_M_M_M_SUM_M_CTST", "MXP_MPRL_M_M_M_SUM_M_CTST",
			"YHP_MPRL_M_M_M_SUM_M_CTST", "YLP_MPRL_M_M_M_SUM_M_CTST", "JWP_MPRL_M_M_M_SUM_M_CTST",
			"HHW_MPRL_M_M_M_SUM_M_CTST", "JHP_MPRL_M_M_M_SUM_M_CTST", "PHP_MPRL_M_M_M_SUM_M_CTST",
			"GZH_MPRL_M_M_M_SUM_M_CTST", "TSP_MPRL_M_M_M_SUM_M_CTST", "DPP_MPRL_M_M_M_SUM_M_CTST" };
	/**
	 * 年初生产能力_6000千瓦及以上火电燃气指标
	 */
	static final String[] NCSC_HD_RQ = new String[] { "HZL_MPRL_M_M_M_SUM_M_CTST", "GQL_MPRL_M_M_M_SUM_M_CTST" };
	/**
	 * 年初生产能力_6000千瓦及以上火电其他指标
	 */
	static final String[] NCSC_HD_QT = new String[] { "SWF_MPRL_M_M_M_SUM_M_CTST" };
	/**
	 * 年初生产能力_6000千瓦及以上风电全部指标
	 */
	static final String[] NCSC_FD = new String[] { "SBS_MPRL_M_M_M_SUM_M_CTST", "ZJW_MPRL_M_M_M_SUM_M_CTST",
			"HLW_MPRL_M_M_M_SUM_M_CTST", "DBW_MPRL_M_M_M_SUM_M_CTST", "PXW_MPRL_M_M_M_SUM_M_CTST",
			"ZYW_MPRL_M_M_M_SUM_M_CTST", "SQW_MPRL_M_M_M_SUM_M_CTST" };
	/**
	 * 年初生产能力_6000千瓦及以上太阳能全部指标
	 */
	static final String[] NCSC_TYN = new String[] { "KFE_MPRL_M_M_M_SUM_M_CTST", "YXX_MPRL_M_M_M_SUM_M_CTST" };

	/**
	 * 发电量_6000千瓦及以上电厂全部指标
	 */
	static final String[] FDL_All = new String[] { "XFJ_FDL_M_M_M_SUM_M_MANL", "FSB_FDL_M_M_M_SUM_M_MANL",
			"QXH_FDL_M_M_M_SUM_M_MANL", "CHH_FDL_M_M_M_SUM_M_MANL", "NSH_FDL_M_M_M_SUM_M_MANL",
			"CTH_FDL_M_M_M_SUM_M_MANL", "LXH_FDL_M_M_M_SUM_M_MANL", "PLT_FDL_M_M_M_SUM_M_MANL",
			"TSQ_FDL_M_M_M_SUM_M_MANL", "LCC_FDL_M_M_M_SUM_M_MANL", "", "SJA_FDL_M_M_M_SUM_M_MANL",
			"SJC_FDL_M_M_M_SUM_M_MANL", "ZHP_FDL_M_M_M_SUM_M_MANL", "HPP_FDL_M_M_M_SUM_M_MANL",
			"ZJP_FDL_M_M_M_SUM_M_MANL", "SGY_FDL_M_M_M_SUM_M_MANL", "MMP_FDL_M_M_M_SUM_M_MANL",
			"MXP_FDL_M_M_M_SUM_M_MANL", "YHP_FDL_M_M_M_SUM_M_MANL", "YLP_FDL_M_M_M_SUM_M_MANL",
			"JWP_FDL_M_M_M_SUM_M_MANL", "HHW_FDL_M_M_M_SUM_M_MANL", "JHP_FDL_M_M_M_SUM_M_MANL",
			"PHP_FDL_M_M_M_SUM_M_MANL", "GZH_FDL_M_M_M_SUM_M_MANL", "TSP_FDL_M_M_M_SUM_M_MANL",
			"DPP_FDL_M_M_M_SUM_M_MANL", "HZL_FDL_M_M_M_SUM_M_MANL", "GQL_FDL_M_M_M_SUM_M_MANL",
			"SWF_FDL_M_M_M_SUM_M_MANL", "SBS_FDL_M_M_M_SUM_M_MANL", "ZJW_FDL_M_M_M_SUM_M_MANL",
			"LCC_FDL_M_M_M_SUM_M_MANL", "", "HLW_FDL_M_M_M_SUM_M_MANL", "DBW_FDL_M_M_M_SUM_M_MANL",
			"PXW_FDL_M_M_M_SUM_M_MANL", "ZYW_FDL_M_M_M_SUM_M_MANL", "SQW_FDL_M_M_M_SUM_M_MANL" };
	/**
	 * 发电量_6000千瓦及以上水电全部指标
	 */
	static final String[] FDL_SD = new String[] { "XFJ_FDL_M_M_M_SUM_M_MANL", "FSB_FDL_M_M_M_SUM_M_MANL",
			"QXH_FDL_M_M_M_SUM_M_MANL", "CHH_FDL_M_M_M_SUM_M_MANL", "NSH_FDL_M_M_M_SUM_M_MANL",
			"CTH_FDL_M_M_M_SUM_M_MANL", "LXH_FDL_M_M_M_SUM_M_MANL", "PLT_FDL_M_M_M_SUM_M_MANL",
			"TSQ_FDL_M_M_M_SUM_M_MANL", "LCC_FDL_M_M_M_SUM_M_MANL", "" };
	/**
	 * 发电量_6000千瓦及以上火电全部指标
	 */
	static final String[] FDL_HD = new String[] { "SJA_FDL_M_M_M_SUM_M_MANL", "SJC_FDL_M_M_M_SUM_M_MANL",
			"ZHP_FDL_M_M_M_SUM_M_MANL", "HPP_FDL_M_M_M_SUM_M_MANL", "ZJP_FDL_M_M_M_SUM_M_MANL",
			"SGY_FDL_M_M_M_SUM_M_MANL", "MMP_FDL_M_M_M_SUM_M_MANL", "MXP_FDL_M_M_M_SUM_M_MANL",
			"YHP_FDL_M_M_M_SUM_M_MANL", "YLP_FDL_M_M_M_SUM_M_MANL", "JWP_FDL_M_M_M_SUM_M_MANL",
			"HHW_FDL_M_M_M_SUM_M_MANL", "JHP_FDL_M_M_M_SUM_M_MANL", "PHP_FDL_M_M_M_SUM_M_MANL",
			"GZH_FDL_M_M_M_SUM_M_MANL", "TSP_FDL_M_M_M_SUM_M_MANL", "DPP_FDL_M_M_M_SUM_M_MANL",
			"HZL_FDL_M_M_M_SUM_M_MANL", "GQL_FDL_M_M_M_SUM_M_MANL", "SWF_FDL_M_M_M_SUM_M_MANL" };
	/**
	 * 年初生产能力_6000千瓦及以上火电燃煤指标
	 */
	static final String[] FDL_HD_RM = new String[] { "SJA_FDL_M_M_M_SUM_M_MANL", "SJC_FDL_M_M_M_SUM_M_MANL",
			"ZHP_FDL_M_M_M_SUM_M_MANL", "HPP_FDL_M_M_M_SUM_M_MANL", "ZJP_FDL_M_M_M_SUM_M_MANL",
			"SGY_FDL_M_M_M_SUM_M_MANL", "MMP_FDL_M_M_M_SUM_M_MANL", "MXP_FDL_M_M_M_SUM_M_MANL",
			"YHP_FDL_M_M_M_SUM_M_MANL", "YLP_FDL_M_M_M_SUM_M_MANL", "JWP_FDL_M_M_M_SUM_M_MANL",
			"HHW_FDL_M_M_M_SUM_M_MANL", "JHP_FDL_M_M_M_SUM_M_MANL", "PHP_FDL_M_M_M_SUM_M_MANL",
			"GZH_FDL_M_M_M_SUM_M_MANL", "TSP_FDL_M_M_M_SUM_M_MANL", "DPP_FDL_M_M_M_SUM_M_MANL" };
	/**
	 * 年初生产能力_6000千瓦及以上火电燃气指标
	 */
	static final String[] FDL_HD_RQ = new String[] { "HZL_FDL_M_M_M_SUM_M_MANL", "GQL_FDL_M_M_M_SUM_M_MANL" };
	/**
	 * 年初生产能力_6000千瓦及以上火电其他指标
	 */
	static final String[] FDL_HD_QT = new String[] { "SWF_FDL_M_M_M_SUM_M_MANL" };
	/**
	 * 发电量_6000千瓦及以上风电全部指标
	 */
	static final String[] FDL_FD = new String[] { "SBS_FDL_M_M_M_SUM_M_MANL", "ZJW_FDL_M_M_M_SUM_M_MANL",
			"HLW_FDL_M_M_M_SUM_M_MANL", "DBW_FDL_M_M_M_SUM_M_MANL", "PXW_FDL_M_M_M_SUM_M_MANL",
			"ZYW_FDL_M_M_M_SUM_M_MANL", "SQW_FDL_M_M_M_SUM_M_MANL" };
	/**
	 * 发电量_6000千瓦及以上太阳能全部指标
	 */
	static final String[] FDL_TYN = new String[] { "KFE_FDL_M_M_M_SUM_M_MANL", "YXX_FDL_M_M_M_SUM_M_MANL" };

	/**
	 * 报表左上角信息设置
	 */
	static final String[] REPORT_DESC = new String[] { "CEC102-发电生产能力（年报）", "424983" };

	/**
	 * 更新IndicatorMapperReport1数据
	 */
	@Transactional
	@SuppressWarnings("static-access")
	public Boolean updateDatas(Long time) {
		Date date = new Date(time);
		Calendar cal = Calendar.getInstance();
		cal.setTime(date);
		// 设置用户选择的月份的当月为结束时间
		long endTime = time;
		// 将时间设置为当前年份第一天，暨1月为开始时间
		cal.set(Calendar.DAY_OF_YEAR, 1);
		long beginTime = cal.getTime().getTime();
		// 获取去年12月份的时间为过去时间
		cal.add(cal.MONTH, -1);
		long oldTime = cal.getTime().getTime();
		// 查询IndicatorMapperReport1表中当前时间戳是否存在数据
		List<IndicatorMapperReport1> historyReports = reportRepository.findByTimeOrderByRowIndexAsc(endTime);
		List<IndicatorMapperReport1> reportData = null;
		// 如果存在数据,则需要保留之前页面手动录入的数据。
		if (historyReports.size() != 0) {
			reportData = setUpdateReportData(historyReports, beginTime, endTime, oldTime);
		} else {// 如果不存在数据，则直接更新数据
			reportData = new ArrayList<>();
			for (int i = 0; i < ALL_ROW_NAME.length; i++) {
				IndicatorMapperReport1 report = new IndicatorMapperReport1();
				// 设置时间
				report.setTime(endTime);
				// 设置行号
				report.setRowIndex(i + 1);
				String[] rowName = ALL_ROW_NAME[i].split("##");
				// 设置电压等级
				report.setDydj(rowName[0]);
				// 设置指标代码
				report.setZbdm(rowName[1]);
				// 设置组织机构代码
				report.setZzjgdm("");
				report.setNcscnl(0.0);
				report.setNcscnl_hj(0.0);
				report.setNcscnl_jjxz(0.0);
				report.setNcscnl_jgxz(0.0);
				report.setNcscnl_qtxz(0.0);
				report.setBnjsnl(0.0);
				report.setNmscnl(0.0);
				report.setFdl(0.0);
				reportData.add(report);
			}
			reportData = setUpdateReportData(reportData,beginTime, endTime, oldTime);
		}
		List<IndicatorMapperReport1> save = reportRepository.save(reportData);
		return save.size() > 0 ? true : false;
	}

	/**
	 * 从MapperData更新的数据，保存之前手动录入的数据。
	 * 
	 * @param historyReports
	 * @param beginTime
	 * @param endTime
	 * @param oldTime
	 * @return
	 */
	private List<IndicatorMapperReport1> setUpdateReportData(List<IndicatorMapperReport1> historyReports,
			Long beginTime, Long endTime, Long oldTime) {
		// 设置第1行数据
		// 6000千瓦及以上指标
		List<String> indicatorCodes = Arrays.asList(NCSC_All);
		// 查询6000千瓦及以上指标去年12月份的数据，求和作为年初生产能力
		List<IndicatorMapperData> findAll = findMapperDatas(indicatorCodes, oldTime, oldTime);
		// 1. 计算年初生产能力
		double ncSum = mapperDataListSum(findAll);
		// 2. 计算年末生产能力
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		double nmSum = mapperDataListSum(findAll);
		// 3. 计算发电量
		indicatorCodes = Arrays.asList(FDL_All);
		findAll = findMapperDatas(indicatorCodes, beginTime, endTime);
		double fdlSum = mapperDataListSum(findAll);
		historyReports = setUpdateColumnData(0, historyReports, ncSum + historyReports.get(ROW).getNcscnl(),
				nmSum + historyReports.get(ROW).getNcscnl(), fdlSum + historyReports.get(ROW).getFdl());

		// 设置第2行数据
		indicatorCodes = Arrays.asList(NCSC_SD);
		// 1.设置水电年初生产能力
		findAll = findMapperDatas(indicatorCodes, oldTime, oldTime);
		double sdNcSum = mapperDataListSum(findAll);
		// 2.设置水电年末生产能力
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		double sdNmSum = mapperDataListSum(findAll);
		// 3. 计算水电发电量
		indicatorCodes = Arrays.asList(FDL_SD);
		findAll = findMapperDatas(indicatorCodes, beginTime, endTime);
		double sdFdlSum = mapperDataListSum(findAll);
		historyReports = setUpdateColumnData(1, historyReports, sdNcSum + historyReports.get(ROW).getNcscnl(),
				sdNmSum + historyReports.get(ROW).getNmscnl(), sdFdlSum + historyReports.get(ROW).getFdl());

		// 设置第3行数据
		indicatorCodes = Arrays.asList(NCSC_HD);
		// 1.设置火电年初生产能力
		findAll = findMapperDatas(indicatorCodes, oldTime, oldTime);
		double hdNcSum = mapperDataListSum(findAll);
		// 2.设置火电年末生产能力
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		double hdNmSum = mapperDataListSum(findAll);
		// 3. 计算火电发电量
		indicatorCodes = Arrays.asList(FDL_HD);
		findAll = findMapperDatas(indicatorCodes, beginTime, endTime);
		double hdFdlSum = mapperDataListSum(findAll);
		historyReports = setUpdateColumnData(2, historyReports, hdNcSum + historyReports.get(ROW + 1).getNcscnl(),
				hdNmSum + historyReports.get(ROW + 1).getNmscnl(), hdFdlSum + historyReports.get(ROW + 1).getFdl());

		// 设置第5行数据
		indicatorCodes = Arrays.asList(NCSC_FD);
		// 1.设置风电年初生产能力
		findAll = findMapperDatas(indicatorCodes, oldTime, oldTime);
		double fdNcSum = mapperDataListSum(findAll);
		// 2.设置风电年末生产能力
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		double fdNmSum = mapperDataListSum(findAll);
		// 3. 计算风电发电量
		indicatorCodes = Arrays.asList(FDL_FD);
		findAll = findMapperDatas(indicatorCodes, beginTime, endTime);
		double fdFdlSum = mapperDataListSum(findAll);
		historyReports = setUpdateColumnData(4, historyReports, fdNcSum + historyReports.get(ROW + 2).getNcscnl(),
				fdNmSum + historyReports.get(ROW + 2).getNmscnl(), fdFdlSum + historyReports.get(ROW + 2).getFdl());

		// 设置第6行数据
		indicatorCodes = Arrays.asList(NCSC_TYN);
		// 1.设置太阳能年初生产能力
		findAll = findMapperDatas(indicatorCodes, oldTime, oldTime);
		double tynNcSum = mapperDataListSum(findAll);
		// 2.设置太阳能年末生产能力
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		double tynNmSum = mapperDataListSum(findAll);
		// 3. 计算太阳能发电量
		indicatorCodes = Arrays.asList(FDL_TYN);
		findAll = findMapperDatas(indicatorCodes, beginTime, endTime);
		double tynFdlSum = mapperDataListSum(findAll);
		historyReports = setUpdateColumnData(5, historyReports, tynNcSum + historyReports.get(ROW + 3).getNcscnl(),
				tynNmSum + historyReports.get(ROW + 3).getNmscnl(), tynFdlSum + historyReports.get(ROW + 3).getFdl());

		// 设置第8行数据
		historyReports = setUpdateColumnData(7, historyReports, ncSum, nmSum, fdlSum);

		// 设置第9行数据
		historyReports = setUpdateColumnData(8, historyReports, sdNcSum, sdNmSum, sdFdlSum);

		// 设置第11行数据
		historyReports = setUpdateColumnData(10, historyReports, hdNcSum, hdNmSum, hdFdlSum);

		// 设置12行数据
		// 1.设置火电燃煤年初生产能力
		indicatorCodes = Arrays.asList(NCSC_HD_RM);
		findAll = findMapperDatas(indicatorCodes, oldTime, oldTime);
		double hdRmNcSum = mapperDataListSum(findAll);
		// 2.设置火电燃煤年末生产能力
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		double hdRmNmSum = mapperDataListSum(findAll);
		// 3. 计算火电燃煤发电量
		indicatorCodes = Arrays.asList(FDL_HD_RM);
		findAll = findMapperDatas(indicatorCodes, beginTime, endTime);
		double hdRmFdlSum = mapperDataListSum(findAll);
		historyReports = setUpdateColumnData(11, historyReports, hdRmNcSum, hdRmNmSum, hdRmFdlSum);

		// 设置14行数据
		// 1.设置火电燃气年初生产能力
		indicatorCodes = Arrays.asList(NCSC_HD_RQ);
		findAll = findMapperDatas(indicatorCodes, oldTime, oldTime);
		double hdRqNcSum = mapperDataListSum(findAll);
		// 2.设置火电燃气年末生产能力
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		double hdRqNmSum = mapperDataListSum(findAll);
		// 3. 计算火电燃气发电量
		indicatorCodes = Arrays.asList(FDL_HD_RQ);
		findAll = findMapperDatas(indicatorCodes, beginTime, endTime);
		double hdRqFdlSum = mapperDataListSum(findAll);
		historyReports = setUpdateColumnData(13, historyReports, hdRqNcSum, hdRqNmSum, hdRqFdlSum);

		// 设置18行数据
		// 1.设置火电其他年初生产能力
		indicatorCodes = Arrays.asList(NCSC_HD_QT);
		findAll = findMapperDatas(indicatorCodes, oldTime, oldTime);
		double hdQtNcSum = mapperDataListSum(findAll);
		// 2.设置火电其他年末生产能力
		findAll = findMapperDatas(indicatorCodes, endTime, endTime);
		double hdQtNmSum = mapperDataListSum(findAll);
		// 3. 计算火电其他发电量
		indicatorCodes = Arrays.asList(FDL_HD_QT);
		findAll = findMapperDatas(indicatorCodes, beginTime, endTime);
		double hdQtFdlSum = mapperDataListSum(findAll);
		historyReports = setUpdateColumnData(17, historyReports, hdQtNcSum, hdQtNmSum, hdQtFdlSum);

		// 设置21行数据
		historyReports = setUpdateColumnData(20, historyReports, hdQtNcSum, hdQtNmSum, hdQtFdlSum);

		// 设置23行数据
		historyReports = setUpdateColumnData(22, historyReports, fdNcSum, fdNmSum, fdFdlSum);

		// 设置24行数据
		historyReports = setUpdateColumnData(23, historyReports, tynNcSum, tynNmSum, tynFdlSum);

		// 遍历6000千瓦及以上电厂明细指标
		for (int i = 0; i < NCSC_All.length; i++) {
			IndicatorMapperData indicatorMapperData = null;
			// 1.设置电厂年初生产能力
			double dcNcSum = 0.0;
			if (!NCSC_All[i].equals("")) {
				indicatorMapperData = mapperDataService.findByIndicatorCodeAndTime(NCSC_All[i], oldTime);
				dcNcSum = indicatorMapperData.getValue();
			}
			// 2.设置电厂年末生产能力
			double dcNmSum = 0.0;
			if (!NCSC_All[i].equals("")) {
				indicatorMapperData = mapperDataService.findByIndicatorCodeAndTime(NCSC_All[i], endTime);
				dcNmSum = indicatorMapperData.getValue();
			}
			// 3. 计算电厂发电量
			double dcFdlSum = 0.0;
			if (!FDL_All[i].equals("")) {
				List<IndicatorMapperData> list = mapperDataService
						.findByIndicatorCodeAndTimeGreaterThanEqualAndTimeLessThanEqual(FDL_All[i], beginTime, endTime);
				DoubleSummaryStatistics collect = list.parallelStream().map(IndicatorMapperData::getValue)
						.collect(Collectors.summarizingDouble(Double::doubleValue));
				dcFdlSum = collect.getSum();
			}
			historyReports = setUpdateColumnData(30 + i, historyReports, dcNcSum, dcNmSum, dcFdlSum);
		}

		return historyReports;
	}

	

	/**
	 * 设置MapperDataList查询方法
	 */
	private List<IndicatorMapperData> findMapperDatas(List<String> indicatorCodes, Long beginTime, Long endTime) {
		List<SearchFilter> searchFilters = new ArrayList<>();
		searchFilters.add(SearchFilter.in("indicatorCode", indicatorCodes));
		searchFilters.add(SearchFilter.ge("time", beginTime));
		searchFilters.add(SearchFilter.le("time", endTime));
		Specification<IndicatorMapperData> buildAndSpec = JpaSearchUtils.buildAndSpec(searchFilters);
		List<IndicatorMapperData> findAll = mapperDataService.findAll(buildAndSpec);
		return findAll;
	}

	/**
	 * 设置MapperDataList求和方法
	 */
	private double mapperDataListSum(List<IndicatorMapperData> mapperDataList) {
		return mapperDataList.stream().mapToDouble(x -> x.getValue()).summaryStatistics().getSum();
	}

	/**
	 * 设置每行的年初生产能力、年末生产能力、本年减少能力和发电量
	 */
	private List<IndicatorMapperReport1> setUpdateColumnData(Integer index, List<IndicatorMapperReport1> historyReports,
			Double ncscnl, Double nmscnl, Double fdl) {
		// 1.设置年初生产能力
		historyReports.get(index).setNcscnl(ncscnl);
		// 2.设置年末生产能力
		historyReports.get(index).setNmscnl(nmscnl);
		// 3. 计算本年减少能力值
		historyReports.get(index)
				.setBnjsnl(historyReports.get(index).getNcscnl() - historyReports.get(index).getNmscnl());
		// 4. 计算发电量
		historyReports.get(index).setFdl(fdl);
		return historyReports;
	}

	/**
	 * 查询报表数据
	 * 
	 * @param time
	 * @return
	 */
	public List<IndicatorMapperReport1> findByTime(Long time) {
		return reportRepository.findByTimeOrderByRowIndexAsc(time);
	}

	/**
	 * 导出报表
	 */
	public HSSFWorkbook exportReport(List<IndicatorMapperReport1> reportDataList) {
		// 创建工作簿
		HSSFWorkbook workBook = new HSSFWorkbook();
		// 创建工作表 工作表的名字叫helloWorld
		HSSFSheet sheet = workBook.createSheet("工作表1");
		// 创建左上角固定报表描述表格
		sheet = creatRepotDesc(workBook, sheet, reportDataList.get(0).getTime());

		// 创建下方主报表
		sheet = creatMainRepot(workBook, sheet, reportDataList);
		return workBook;
	}

	/**
	 * 创建左上角报表
	 * 
	 * @param sheet
	 * @param time
	 * @return
	 */
	public HSSFSheet creatRepotDesc(HSSFWorkbook workBook, HSSFSheet sheet, Long time) {
		// 设置字体样式
		HSSFFont font = workBook.createFont();
		font.setColor(HSSFFont.COLOR_RED);
		

		// 创建第1行
		HSSFRow row_1 = sheet.createRow(0);
		HSSFCell row_1_cell_1 = row_1.createCell(0);
		row_1_cell_1.setCellValue("*");
		HSSFCell row_1_cell_2 = row_1.createCell(1);
		row_1_cell_2.setCellValue("");
		HSSFCell row_1_cell_3 = row_1.createCell(2);
		row_1_cell_3.setCellValue("1.本表头供系统识别使用；\r\n" + "2.各单位根据具体情况填写单位代码、单位名称、填报人；\r\n" + "3.本表头报告期必须与此报表报告期一致；\r\n"
				+ "4.指标代码必须按照规律编；\r\n" + "5.不可改动报表格式。");
		// 创建第2行
		HSSFRow row_2 = sheet.createRow(1);
		HSSFCell row_2_cell_1 = row_2.createCell(0);
		HSSFRichTextString richString = new HSSFRichTextString("广东粤电集团有限公司");
		richString.applyFont(font);
		row_2_cell_1.setCellValue(richString);
		HSSFCell row_2_cell_2 = row_2.createCell(1);
		row_2_cell_2.setCellValue("（单位名称）");

		// 创建第3行
		HSSFRow row_3 = sheet.createRow(2);
		HSSFCell row_3_cell_1 = row_3.createCell(0);
		richString = new HSSFRichTextString("9808000001");
		richString.applyFont(font);
		row_3_cell_1.setCellValue(richString);
		HSSFCell row_3_cell_2 = row_3.createCell(1);
		row_3_cell_2.setCellValue("（单位代码）");

		// 创建第4行
		HSSFRow row_4 = sheet.createRow(3);
		HSSFCell row_4_cell_1 = row_4.createCell(0);
		richString = new HSSFRichTextString("统计专责");
		richString.applyFont(font);
		row_4_cell_1.setCellValue(richString);
		HSSFCell row_4_cell_2 = row_4.createCell(1);
		row_4_cell_2.setCellValue("（填报人）");

		// 创建第5行
		HSSFRow row_5 = sheet.createRow(4);
		HSSFCell row_5_cell_1 = row_5.createCell(0);
		row_5_cell_1.setCellValue(REPORT_DESC[0]);
		HSSFCell row_5_cell_2 = row_5.createCell(1);
		row_5_cell_2.setCellValue("（表名）");

		// 创建第6行
		HSSFRow row_6 = sheet.createRow(5);
		HSSFCell row_6_cell_1 = row_6.createCell(0);
		row_6_cell_1.setCellValue(REPORT_DESC[1]);
		HSSFCell row_6_cell_2 = row_6.createCell(1);
		row_6_cell_2.setCellValue("（表号）");

		// 创建第7行
		HSSFRow row_7 = sheet.createRow(6);
		HSSFCell row_7_cell_1 = row_7.createCell(0);
		row_7_cell_1.setCellValue("1");
		HSSFCell row_7_cell_2 = row_7.createCell(1);
		row_7_cell_2.setCellValue("（期别）");

		// 创建第8行
		Calendar cal = Calendar.getInstance();
		Date date = new Date(time);
		cal.setTime(date);
		HSSFRow row_8 = sheet.createRow(7);
		HSSFCell row_8_cell_1 = row_8.createCell(0);
		row_8_cell_1.setCellValue(new HSSFRichTextString(String.valueOf(cal.get(Calendar.YEAR))));
		HSSFCell row_8_cell_2 = row_8.createCell(1);
		row_8_cell_2.setCellValue("年");
		HSSFCell row_8_cell_3 = row_8.createCell(2);
		row_8_cell_3.setCellValue(new HSSFRichTextString(String.valueOf(cal.get(Calendar.MONTH) + 1)));
		HSSFCell row_8_cell_4 = row_8.createCell(3);
		row_8_cell_4.setCellValue("月");
		HSSFCell row_8_cell_5 = row_8.createCell(4);
		row_8_cell_5.setCellValue("");

		sheet.addMergedRegion(new CellRangeAddress(0, 6, 2, 2));

		return sheet;
	}

	/**
	 * 创建下方主要表格
	 * 
	 * @param sheet
	 * @param reportDataList
	 * @return
	 */
	public HSSFSheet creatMainRepot(HSSFWorkbook workBook, HSSFSheet sheet,
			List<IndicatorMapperReport1> reportDataList) {
		// 创建红色居中样式
		HSSFCellStyle cellStyle = workBook.createCellStyle();
		// 设置居中样式
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 设置字体样式
		HSSFFont font = workBook.createFont();
		font.setColor(HSSFFont.COLOR_RED);
		cellStyle.setFont(font);
				
				
		// 创建第9行
		HSSFRow row_9 = sheet.createRow(8);
		HSSFCell row_9_cell_1 = row_9.createCell(0);
		row_9_cell_1.setCellValue("发电生产能力");
		row_9_cell_1.setCellStyle(cellStyle);

		// 创建第10行
		HSSFRow row_10 = sheet.createRow(9);
		HSSFCell row_10_cell_1 = row_10.createCell(0);
		row_10_cell_1.setCellValue("综合填报单位：  （章）");
		HSSFCell row_10_cell_2 = row_10.createCell(1);
		row_10_cell_2.setCellValue("");
		HSSFCell row_10_cell_3 = row_10.createCell(2);
		row_10_cell_3.setCellValue("");
		HSSFCell row_10_cell_4 = row_10.createCell(3);
		row_10_cell_4.setCellValue("");
		HSSFCell row_10_cell_5 = row_10.createCell(4);
		row_10_cell_5.setCellValue("");
		HSSFCell row_10_cell_6 = row_10.createCell(5);
		row_10_cell_6.setCellValue("");
		HSSFCell row_10_cell_7 = row_10.createCell(6);
		row_10_cell_7.setCellValue("");
		HSSFCell row_10_cell_8 = row_10.createCell(7);
		row_10_cell_8.setCellValue("YYYY");
		HSSFCell row_10_cell_9 = row_10.createCell(8);
		row_10_cell_9.setCellValue("");
		HSSFCell row_10_cell_10 = row_10.createCell(9);
		row_10_cell_10.setCellValue("");
		HSSFCell row_10_cell_11 = row_10.createCell(10);
		row_10_cell_11.setCellValue("");
		HSSFCell row_10_cell_12 = row_10.createCell(11);
		row_10_cell_12.setCellValue("");

		// 创建第11行
		HSSFRow row_11 = sheet.createRow(10);
		HSSFCell row_11_cell_1 = row_11.createCell(0);
		row_11_cell_1.setCellValue("电压等级");
		HSSFCell row_11_cell_2 = row_11.createCell(1);
		row_11_cell_2.setCellValue("指标代码");
		HSSFCell row_11_cell_3 = row_11.createCell(2);
		row_11_cell_3.setCellValue("组织机构代码");
		HSSFCell row_11_cell_4 = row_11.createCell(3);
		row_11_cell_4.setCellValue("年初生产能力");
		HSSFCell row_11_cell_5 = row_11.createCell(4);
		row_11_cell_5.setCellValue("本年新增能力（千瓦）");
		HSSFCell row_11_cell_6 = row_11.createCell(8);
		row_11_cell_6.setCellValue("本年减少能力");
		HSSFCell row_11_cell_7 = row_11.createCell(9);
		row_11_cell_7.setCellValue("年末生产能力");
		HSSFCell row_11_cell_8 = row_11.createCell(10);
		row_11_cell_8.setCellValue("发电量");
		HSSFCell row_11_cell_9 = row_11.createCell(11);
		row_11_cell_9.setCellValue("备注");

		// 创建第12行
		HSSFRow row_12 = sheet.createRow(11);
		HSSFCell row_12_cell_1 = row_12.createCell(3);
		row_12_cell_1.setCellValue("（千瓦）");
		HSSFCell row_12_cell_2 = row_12.createCell(4);
		row_12_cell_2.setCellValue("合计");
		HSSFCell row_12_cell_3 = row_12.createCell(5);
		row_12_cell_3.setCellValue("基建新增");
		HSSFCell row_12_cell_4 = row_12.createCell(6);
		row_12_cell_4.setCellValue("技改新增");
		HSSFCell row_12_cell_5 = row_12.createCell(7);
		row_12_cell_5.setCellValue("其他新增");
		HSSFCell row_12_cell_6 = row_12.createCell(8);
		row_12_cell_6.setCellValue("（千瓦）");
		HSSFCell row_12_cell_7 = row_12.createCell(9);
		row_12_cell_7.setCellValue("（千瓦）");
		HSSFCell row_12_cell_8 = row_12.createCell(10);
		row_12_cell_8.setCellValue("（万千瓦时）");

		// 创建第13行
		HSSFRow row_13 = sheet.createRow(12);
		HSSFCell row_13_cell_1 = row_13.createCell(0);
		row_13_cell_1.setCellValue("甲");
		HSSFCell row_13_cell_2 = row_13.createCell(1);
		row_13_cell_2.setCellValue("1");
		HSSFCell row_13_cell_3 = row_13.createCell(2);
		row_13_cell_3.setCellValue("2");
		HSSFCell row_13_cell_4 = row_13.createCell(3);
		row_13_cell_4.setCellValue("3");
		HSSFCell row_13_cell_5 = row_13.createCell(4);
		row_13_cell_5.setCellValue("4");
		HSSFCell row_13_cell_6 = row_13.createCell(5);
		row_13_cell_6.setCellValue("5");
		HSSFCell row_13_cell_7 = row_13.createCell(6);
		row_13_cell_7.setCellValue("6");
		HSSFCell row_13_cell_8 = row_13.createCell(7);
		row_13_cell_8.setCellValue("7");
		HSSFCell row_13_cell_9 = row_13.createCell(8);
		row_13_cell_9.setCellValue("8");
		HSSFCell row_13_cell_10 = row_13.createCell(9);
		row_13_cell_10.setCellValue("9");
		HSSFCell row_13_cell_11 = row_13.createCell(10);
		row_13_cell_11.setCellValue("10");
		HSSFCell row_13_cell_12 = row_13.createCell(11);
		row_13_cell_12.setCellValue("11");

		for (IndicatorMapperReport1 report : reportDataList) {
			HSSFRow row = sheet.createRow(report.getRowIndex() + ROW_INDEX);
			// 设置第1列
			HSSFCell cell_1 = row.createCell(0);
			cell_1.setCellValue(report.getDydj());
			// 设置第2列
			HSSFCell cell_2 = row.createCell(1);
			cell_2.setCellValue(report.getZbdm());
			// 设置第3列
			HSSFCell cell_3 = row.createCell(2);
			cell_3.setCellValue(report.getZzjgdm());
			// 设置第4列
			HSSFCell cell_4 = row.createCell(3);
			cell_4.setCellValue(report.getNcscnl());
			// 设置第5列
			HSSFCell cell_5 = row.createCell(4);
			cell_5.setCellValue(report.getNcscnl_hj());
			// 设置第6列
			HSSFCell cell_6 = row.createCell(5);
			cell_6.setCellValue(report.getNcscnl_jjxz());
			// 设置第7列
			HSSFCell cell_7 = row.createCell(6);
			cell_7.setCellValue(report.getNcscnl_jgxz());
			// 设置第8列
			HSSFCell cell_8 = row.createCell(7);
			cell_8.setCellValue(report.getNcscnl_qtxz());
			// 设置第9列
			HSSFCell cell_9 = row.createCell(8);
			cell_9.setCellValue(report.getBnjsnl());
			// 设置第10列
			HSSFCell cell_10 = row.createCell(9);
			cell_10.setCellValue(report.getNmscnl());
			// 设置第11列
			HSSFCell cell_11 = row.createCell(10);
			cell_11.setCellValue(report.getFdl());
			// 设置第12列
			HSSFCell cell_12 = row.createCell(11);
			cell_12.setCellValue(report.getBz());
		}

		// 合并发点成产能力单元格
		sheet.addMergedRegion(new CellRangeAddress(8, 8, 0, 11));
		// 合并YYYY
		sheet.addMergedRegion(new CellRangeAddress(9, 9, 4, 7));
		// 合并电压等级单元格
		sheet.addMergedRegion(new CellRangeAddress(10, 11, 0, 0));
		// 合并指标代码
		sheet.addMergedRegion(new CellRangeAddress(10, 11, 1, 1));
		// 合并组织结构代码
		sheet.addMergedRegion(new CellRangeAddress(10, 11, 2, 2));
		// 合并本年新增能力（千瓦）
		sheet.addMergedRegion(new CellRangeAddress(10, 10, 4, 7));
		// 合并备注
		sheet.addMergedRegion(new CellRangeAddress(10, 11, 11, 11));
		return sheet;
	}

}
